package com.navi.mould1.service.impl;

import com.navi.bean.common.ToolToolgLineFabCorrelation;
import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.activation_report.ActivationReportBean;
import com.navi.bean.out.activation_report.ActivationReportDataBean;
import com.navi.bean.out.activation_report.ActivationReportGridOut;
import com.navi.bean.sql.SqlVo;
import com.navi.core.repository.NaviRepository;
import com.navi.core.util.DateUtils;
import com.navi.core.util.XxdUtils;
import com.navi.mould1.reportUtils.LKingSortUtil;
import com.navi.mould1.service.IActivation2ReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;
import static com.navi.mould1.reportUtils.ComputeUtil.getAverageRate;
import static com.navi.mould1.reportUtils.reportUtil.getCurrenDay;
import static java.util.Comparator.comparing;
import static java.util.stream.Collectors.maxBy;

@Slf4j
@Service
public class Activation2ReportServiceImpl implements IActivation2ReportService {

    @Autowired
    private NaviRepository dao;


    public ActivationReportBean getGridData(ConditionBean conditions) {
        final Timestamp currentTimestamp = XxdUtils.getCurrentTimestamp(); //实时时间
        final String currentDay = getCurrenDay(currentTimestamp);          //根据8:00-8:00的规则，当前时间属于哪一天

        List<ToolToolgLineFabCorrelation> toolToolgLineFabCorrelation_list = getTool_toolg_line_fab_correlation();   //查询设备、设备组、线别、厂别之间的关联关系，及中文描述

        List<ActivationReportGridOut> finalGridOut_list = new ArrayList<>();
        //历史数据 - 查询Summary表
        finalGridOut_list.addAll(getSummaryTableData(conditions, toolToolgLineFabCorrelation_list, currentDay));
        //实时数据 - 查询His表
        finalGridOut_list.addAll(getHisTableData(conditions, toolToolgLineFabCorrelation_list, currentDay, currentTimestamp));


        ActivationReportBean reportBean = new ActivationReportBean();
        reportBean.setFab_gridData(finalGridOut_list.stream().filter(data -> "F".equals(data.getLevel())).collect(Collectors.toList()));
        reportBean.setLine_gridData(finalGridOut_list.stream().filter(data -> "L".equals(data.getLevel())).collect(Collectors.toList()));
        reportBean.setToolg_gridData(finalGridOut_list.stream().filter(data -> "G".equals(data.getLevel())).collect(Collectors.toList()));
        reportBean.setTool_gridData(finalGridOut_list.stream().filter(data -> "T".equals(data.getLevel())).collect(Collectors.toList()));
        LKingSortUtil.sort(reportBean.getFab_gridData(), new String[] { "day", "fab_id" }, new boolean[] { false, true });
        LKingSortUtil.sort(reportBean.getLine_gridData(), new String[] { "day", "fab_id", "line_id" }, new boolean[] { false, true, true });
        LKingSortUtil.sort(reportBean.getToolg_gridData(), new String[] { "day", "fab_id", "toolg_id" }, new boolean[] { false, true, true });
        LKingSortUtil.sort(reportBean.getTool_gridData(), new String[] { "day", "fab_id", "line_id", "toolg_id", "tool_id" }, new boolean[] { true, true, true, true, true });
        return reportBean;
    }







    /*** 查询His表的实时数据 **/
    private List<ActivationReportGridOut> getHisTableData(ConditionBean conditions, List<ToolToolgLineFabCorrelation> toolToolgLineFabCorrelation_list, String current_day, Timestamp currentTimestamp) {
        List<ActivationReportGridOut> gridOut_list = new ArrayList<>();

        if (DateUtils.parseDate(conditions.getEnd_time()).getTime() < DateUtils.parseDate(current_day).getTime()) return gridOut_list; //未查询到当前实时天,则返回空;
        Timestamp begin_timestamp = Timestamp.valueOf(current_day + " 08:00:00");
        Timestamp end_timestamp = (new Timestamp(begin_timestamp.getTime() + 24*60*60*1000).getTime() > currentTimestamp.getTime()) ? currentTimestamp : new Timestamp(begin_timestamp.getTime() + 24*60*60*1000);

        //By设备汇总各状态工时
        final SqlVo sqlVo1 = new SqlVo();
        sqlVo1.appendSql("SELECT");
        sqlVo1.appendSql("sum(KEET_SECOND_TODAY) AS second_time,");
        sqlVo1.appendSql("PV_TOOL_STAT,");
        sqlVo1.appendSql("TOOL_ID_FK");
        sqlVo1.appendSql("FROM (");
        sqlVo1.appendSql("SELECT");
        sqlVo1.appendSql("TOOL_ID_FK, TOOL_STAT, PV_TOOL_STAT, CLM_TIMESTAMP, PV_CLM_TIMESTAMP,");
        sqlVo1.appendSql("EVT_TIMESTAMP,");
        sqlVo1.appendSql("KEEP_TIME,");
        sqlVo1.appendSql("CASE");
        sqlVo1.appendSql("WHEN PV_CLM_TIMESTAMP < '"+begin_timestamp+"' THEN");
        sqlVo1.appendSql("TIMESTAMPDIFF(SECOND, '"+begin_timestamp+"', CLM_TIMESTAMP)");
        sqlVo1.appendSql("ELSE");
        sqlVo1.appendSql("TIMESTAMPDIFF(SECOND, PV_CLM_TIMESTAMP, CLM_TIMESTAMP)");
        sqlVo1.appendSql("END AS KEET_SECOND_TODAY");
        sqlVo1.appendSql("FROM");
        sqlVo1.appendSql("his_icim_jn.his_ret_tool_st s");
        sqlVo1.appendSql("WHERE 1=1");
        sqlVo1.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where ((unit_typ = 'MAIN' and tool_cate = 'PROC') OR sum_flg = 'Y'))"); //稼动率默认取主机台，和丝网A、B轨
        if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo1.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where TOOL_CATE = 'PROC' and fab_id_fk in" + jointInSql(conditions.getFab_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo1.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where bay_id in" + jointInSql(conditions.getBay_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getToolg_id())) sqlVo1.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where root_tool_id in (select tool_id_fk from icim_jn.bis_tool_gr where toolg_id in" + jointInSql(conditions.getToolg_id()) + "))");
        if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo1.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where tool_id in" + jointInSql(conditions.getTool_id()) + ")");
        sqlVo1.appendSql("AND CLM_TIMESTAMP BETWEEN '"+begin_timestamp+"' AND '"+end_timestamp+"'");
        sqlVo1.appendSql("ORDER BY");
        sqlVo1.appendSql("CLM_TIMESTAMP DESC");
        sqlVo1.appendSql(") AS main_tab");
        sqlVo1.appendSql("GROUP BY PV_TOOL_STAT, TOOL_ID_FK;");

        List<ActivationReportDataBean> resultDataList_1 = mySql_QueryRunner_QueryListBySql(sqlVo1, ActivationReportDataBean.class);



        //补上当日最新状态数据
        final SqlVo lastStatSqlVo = new SqlVo("select * from his_icim_jn.his_ret_tool_st WHERE 1=1");
        lastStatSqlVo.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where ((unit_typ = 'MAIN' and tool_cate = 'PROC') OR sum_flg = 'Y'))"); //稼动率默认取主机台，和丝网A、B轨
        if (StringUtils.isNotEmpty(conditions.getFab_id())) lastStatSqlVo.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where TOOL_CATE = 'PROC' and fab_id_fk in" + jointInSql(conditions.getFab_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getBay_id())) lastStatSqlVo.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where bay_id in" + jointInSql(conditions.getBay_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getToolg_id())) lastStatSqlVo.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where root_tool_id in (select tool_id_fk from icim_jn.bis_tool_gr where toolg_id in" + jointInSql(conditions.getToolg_id()) + "))");
        if (StringUtils.isNotEmpty(conditions.getTool_id())) lastStatSqlVo.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where tool_id in" + jointInSql(conditions.getTool_id()) + ")");
        lastStatSqlVo.appendSql("AND CLM_TIMESTAMP >= '" + begin_timestamp + "'");
        //lastStatSqlVo.appendSql("AND CLM_TIMESTAMP < '" + end_timestamp + "' AND PV_CLM_TIMESTAMP < '" + end_timestamp + "'");
        lastStatSqlVo.appendSql("ORDER BY CLM_TIMESTAMP DESC");

        List<ActivationReportDataBean> resultDataList_2 = mySql_QueryRunner_QueryListBySql(lastStatSqlVo, ActivationReportDataBean.class);


        //丝网的A、B轨设备
        List <String> bisToolList = dao.findBySQL("select tool_id from icim_jn.bis_tool where sum_flg = 'Y'");

        List<ActivationReportDataBean> dataBean_List = new ArrayList<>();
        dataBean_List.addAll(resultDataList_1);

        resultDataList_1.forEach(data -> {
            int minute = Integer.parseInt(data.getSecond_time().toString()) / 60;
            data.setMinute_time(Long.parseLong(String.valueOf(minute)));
        });

        resultDataList_2.stream().collect(Collectors.groupingBy(data -> data.getTool_id_fk())).forEach((tool_id_fk, list) -> {
            ActivationReportDataBean maxClmTimestamp_Bean = list.stream()
                    .filter(data -> StringUtils.isNotEmpty(data.getClm_timestamp().toString()))
                    .collect(maxBy(comparing(data -> data.getClm_timestamp().getTime())))
                    .orElseGet(ActivationReportDataBean::new);
            if (maxClmTimestamp_Bean != null && maxClmTimestamp_Bean.getClm_timestamp() != null) {
                long diff_second = XxdUtils.getDiffSec(maxClmTimestamp_Bean.getClm_timestamp(), end_timestamp);
                long diff_minute = XxdUtils.getDiffMin(maxClmTimestamp_Bean.getClm_timestamp(), end_timestamp);
                if (diff_second != 0) {
                    ActivationReportDataBean dataBean = new ActivationReportDataBean();
                    dataBean.setTool_id_fk(maxClmTimestamp_Bean.getTool_id_fk());
                    dataBean.setPv_tool_stat(maxClmTimestamp_Bean.getTool_stat());
                    dataBean.setSecond_time(diff_second);
                    dataBean.setMinute_time(diff_minute);
                    dataBean_List.add(dataBean);
                }
            }
        });

        /*** By Tool计算状态时长、所占百分比 **/
        List<ActivationReportGridOut> gridOutBean_by_tool_List = new ArrayList<>();
        dataBean_List.stream().collect(Collectors.groupingBy(data -> data.getTool_id_fk())).forEach((toolIdFk, by_tool_list) -> {
            List<ActivationReportDataBean> dataBean_list = new ArrayList<>();
            by_tool_list.stream().collect(Collectors.groupingBy(data -> data.getPv_tool_stat())).forEach((pvStat, by_day_tool_pvStat_list) -> {
                ActivationReportDataBean dataBean = new ActivationReportDataBean();
                dataBean.setPv_tool_stat(pvStat);
                dataBean.setSecond_time(by_day_tool_pvStat_list.stream().mapToLong(data -> data.getSecond_time()).sum());
                dataBean.setMinute_time(by_day_tool_pvStat_list.stream().mapToLong(data -> data.getMinute_time()).sum());
                dataBean_list.add(dataBean);
            });
            ActivationReportGridOut gridOutBean = new ActivationReportGridOut();
            gridOutBean.setTotal_second(XxdUtils.getDiffSec(begin_timestamp, end_timestamp));
            gridOutBean.setTotal_minute(XxdUtils.getDiffMin(begin_timestamp, end_timestamp));
            dataBean_list.forEach(data -> {
                switch (data.getPv_tool_stat()) {
                    case "RUN" : gridOutBean.setRun_minute(data.getMinute_time()); break;
                    case "DOWN" : gridOutBean.setDown_minute(data.getMinute_time()); break;
                    case "IDLE" : gridOutBean.setIdle_minute(data.getMinute_time()); break;
                    case "PM" : gridOutBean.setPm_minute(data.getMinute_time()); break;
                    case "STOP" : gridOutBean.setStop_minute(data.getMinute_time()); break;
                    case "OTHER" : gridOutBean.setOther_minute(data.getMinute_time()); break;
                }
            });
            List<ToolToolgLineFabCorrelation> toolCorrelation_list = toolToolgLineFabCorrelation_list.stream().filter(data -> toolIdFk.equals(data.getTool_id_fk())).collect(Collectors.toList());
            if (CollectionUtils.isNotEmpty(toolCorrelation_list)) {
                gridOutBean.setLevel("T");
                gridOutBean.setDay(begin_timestamp.toString().substring(0, 10).substring(5));
                gridOutBean.setFab_id_fk(toolCorrelation_list.get(0).getFab_id_fk());
                gridOutBean.setFab_id(toolCorrelation_list.get(0).getFab_id());
                gridOutBean.setLine_id_fk(toolCorrelation_list.get(0).getLine_id_fk());
                gridOutBean.setLine_id(toolCorrelation_list.get(0).getLine_id());
                gridOutBean.setToolg_id_fk(toolCorrelation_list.get(0).getToolg_id_fk());
                gridOutBean.setToolg_id(toolCorrelation_list.get(0).getToolg_id());
                gridOutBean.setTool_id_fk(toolIdFk);
                gridOutBean.setTool_id(toolCorrelation_list.get(0).getTool_id());
                gridOutBean.computeRate();
                gridOutBean.rate2Percent();
                gridOutBean.computeTotalTime();
                gridOutBean_by_tool_List.add(gridOutBean);
            }
        });


        /*** 计算Toolg的工时分布比例 **/
        List<ActivationReportGridOut> gridOutBean_by_toolg_List = new ArrayList<>();
        if (true) {
            gridOutBean_by_tool_List.stream().filter(data -> data.getToolg_id() != null).collect(Collectors.groupingBy(ActivationReportGridOut::getToolg_id)).forEach((toolg, by_toolg_list) -> {
                ActivationReportGridOut gridOutBean = new ActivationReportGridOut();
                by_toolg_list.forEach(data -> {
                    //TODO: 增加特殊逻辑处理:  101只看丝网设备组只看 A.B轨稼动率
                    if ("101A".equals(data.getFab_id_fk()) && "$TSWYS".equals(data.getToolg_id_fk())) {
                        if (!bisToolList.contains(data.getTool_id_fk())) return;
                    }
                    gridOutBean.setRun_minute(gridOutBean.getRun_minute() + data.getRun_minute());
                    gridOutBean.setDown_minute(gridOutBean.getDown_minute() + data.getDown_minute());
                    gridOutBean.setIdle_minute(gridOutBean.getIdle_minute() + data.getIdle_minute());
                    gridOutBean.setPm_minute(gridOutBean.getPm_minute() + data.getPm_minute());
                    gridOutBean.setStop_minute(gridOutBean.getStop_minute() + data.getStop_minute());
                    gridOutBean.setOther_minute(gridOutBean.getOther_minute() + data.getOther_minute());
                    gridOutBean.setTotal_minute(gridOutBean.getTotal_minute() + data.getTotal_minute());
                });
                if (gridOutBean.getTotal_minute() != null && gridOutBean.getTotal_minute() != 0) {
                    gridOutBean.setLevel("G");
                    gridOutBean.setDay(begin_timestamp.toString().substring(0, 10).substring(5));
                    gridOutBean.setFab_id_fk(by_toolg_list.get(0).getFab_id_fk());
                    gridOutBean.setFab_id(by_toolg_list.get(0).getFab_id());
                    gridOutBean.setToolg_id_fk(by_toolg_list.get(0).getToolg_id_fk());
                    gridOutBean.setToolg_id(toolg);
                    gridOutBean.computeRate();
                    gridOutBean.rate2Percent();
                    gridOutBean.computeTotalTime();
                    gridOutBean_by_toolg_List.add(gridOutBean);
                }
            });
        }

        /*** 计算Line的工时分布比例 **/
        List<ActivationReportGridOut> gridOutBean_by_line_List = new ArrayList<>();
        if (true) {
            gridOutBean_by_tool_List.stream().filter(data -> data.getLine_id() != null).collect(Collectors.groupingBy(ActivationReportGridOut::getLine_id)).forEach((line, by_line_list) -> {
                ActivationReportGridOut gridOutBean = new ActivationReportGridOut();
                by_line_list.forEach(data -> {
                    //TODO: 增加特殊逻辑处理:  101只看丝网A.B轨  102A、102B只看串焊
                    if (("101A".equals(data.getFab_id_fk()) && bisToolList.contains(data.getTool_id_fk()))
                            || ("102B".equals(data.getFab_id_fk()) && "102B-TCH".equals(data.getToolg_id_fk()))
                            || ("102A".equals(data.getFab_id_fk()) && "102A-TCH".equals(data.getToolg_id_fk()))) {
                        gridOutBean.setRun_minute(gridOutBean.getRun_minute() + data.getRun_minute());
                        gridOutBean.setDown_minute(gridOutBean.getDown_minute() + data.getDown_minute());
                        gridOutBean.setIdle_minute(gridOutBean.getIdle_minute() + data.getIdle_minute());
                        gridOutBean.setPm_minute(gridOutBean.getPm_minute() + data.getPm_minute());
                        gridOutBean.setStop_minute(gridOutBean.getStop_minute() + data.getStop_minute());
                        gridOutBean.setOther_minute(gridOutBean.getOther_minute() + data.getOther_minute());
                        gridOutBean.setTotal_minute(gridOutBean.getTotal_minute() + data.getTotal_minute());
                    }
                });
                if (gridOutBean.getTotal_minute() != null && gridOutBean.getTotal_minute() != 0) {
                    gridOutBean.setLevel("L");
                    gridOutBean.setDay(begin_timestamp.toString().substring(0, 10).substring(5));
                    gridOutBean.setFab_id_fk(by_line_list.get(0).getFab_id_fk());
                    gridOutBean.setFab_id(by_line_list.get(0).getFab_id());
                    gridOutBean.setLine_id_fk(by_line_list.get(0).getLine_id_fk());
                    gridOutBean.setLine_id(line);
                    gridOutBean.computeRate();
                    gridOutBean.rate2Percent();
                    gridOutBean.computeTotalTime();
                    gridOutBean_by_line_List.add(gridOutBean);
                }
            });
        }

        /*** 计算Fab的工时分布比例 **/
        List<ActivationReportGridOut> gridOutBean_by_fab_List = new ArrayList<>();
        if (true) {
            gridOutBean_by_tool_List.stream().filter(data -> data.getFab_id() != null).collect(Collectors.groupingBy(ActivationReportGridOut::getFab_id)).forEach((fab, by_fab_list) -> {
                ActivationReportGridOut gridOutBean = new ActivationReportGridOut();
                by_fab_list.forEach(data -> {
                    gridOutBean.setDown_minute(gridOutBean.getDown_minute() + data.getDown_minute());
                    gridOutBean.setIdle_minute(gridOutBean.getIdle_minute() + data.getIdle_minute());
                    gridOutBean.setPm_minute(gridOutBean.getPm_minute() + data.getPm_minute());
                    gridOutBean.setStop_minute(gridOutBean.getStop_minute() + data.getStop_minute());
                    gridOutBean.setOther_minute(gridOutBean.getOther_minute() + data.getOther_minute());
                    gridOutBean.setTotal_minute(gridOutBean.getTotal_minute() + data.getTotal_minute());
                });
                gridOutBean.setLevel("F");
                gridOutBean.setDay(begin_timestamp.toString().substring(0, 10).substring(5));
                gridOutBean.setFab_id_fk(by_fab_list.get(0).getFab_id_fk());
                gridOutBean.setFab_id(fab);
                gridOutBean.computeRate();
                gridOutBean.rate2Percent();
                gridOutBean.computeTotalTime();
                gridOutBean_by_fab_List.add(gridOutBean);
            });
        }

        gridOut_list.addAll(gridOutBean_by_tool_List);
        gridOut_list.addAll(gridOutBean_by_toolg_List);
        gridOut_list.addAll(gridOutBean_by_line_List);
        gridOut_list.addAll(gridOutBean_by_fab_List);
        return gridOut_list;
    }

    /*** 查询Summary表的历史数据 **/
    private List<ActivationReportGridOut> getSummaryTableData(ConditionBean conditions, List<ToolToolgLineFabCorrelation> toolToolgLineFabCorrelation_list, String current_day) {
        List<String> date_day_list = getDateStrList(conditions.getStart_time(), conditions.getEnd_time()).stream().filter(date_day -> (!current_day.equals(date_day))).collect(Collectors.toList()); //需要查询Summary的天数

        List<ActivationReportGridOut> resultDataList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(date_day_list)) {
            final SqlVo sqlVo = new SqlVo("SELECT");
            sqlVo.appendSql("SUM_DATE AS day,");
            sqlVo.appendSql("SUM_LVL AS level,");
            sqlVo.appendSql("FAB_ID_FK AS fab_id_fk,");
            sqlVo.appendSql("LINE_ID_FK AS line_id_fk,");
            sqlVo.appendSql("TOOLG_ID AS toolg_id_fk,");
            sqlVo.appendSql("TOOL_ID_FK AS tool_id_fk,");
            sqlVo.appendSql("RUN_RATE AS run_rate,");
            sqlVo.appendSql("DOWN_RATE AS down_rate,");
            sqlVo.appendSql("IDLE_RATE AS idle_rate,");
            sqlVo.appendSql("PM_RATE AS pm_rate,");
            sqlVo.appendSql("STOP_RATE AS stop_rate,");
            sqlVo.appendSql("OTHER_RATE AS other_rate,");
            sqlVo.appendSql("ACTIVATION_RATE AS activation_rate");
            sqlVo.appendSql("FROM icim_jn.sum_tool_st");
            sqlVo.appendSql("WHERE 1=1");
            sqlVo.appendSql("AND SUM_LVL = 'T'");
            sqlVo.appendSql("AND SUM_TIMESTAMP < CURRENT_TIMESTAMP");
            if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo.appendSql("AND FAB_ID_FK in" + jointInSql(conditions.getFab_id()));
            //if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo.appendSql("AND LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
            if (StringUtils.isNotEmpty(conditions.getToolg_id())) sqlVo.appendSql("AND TOOLG_ID in" + jointInSql(conditions.getToolg_id()));
            if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo.appendSql("AND TOOL_ID_FK in" + jointInSql(conditions.getTool_id()));
            if (StringUtils.isNotEmpty(conditions.getTool_type())) sqlVo.appendSql("AND TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where unit_typ = '" + conditions.getTool_type() + "')");
            sqlVo.appendSql("AND SUM_DATE in" + jointInSql(date_day_list.stream().map(data -> data.substring(5)).collect(Collectors.joining(","))));
            sqlVo.appendSql("ORDER BY SUM_TIMESTAMP DESC");

            resultDataList = mySql_QueryRunner_QueryListBySql(sqlVo, ActivationReportGridOut.class);
        }

        // T - 级别数据
        resultDataList.forEach(data -> {
            List<ToolToolgLineFabCorrelation> toolCorrelation_list = new ArrayList<>();
            switch (data.getLevel()) {
                //case "F": toolCorrelation_list = toolToolgLineFabCorrelation_list.stream().filter(toolToolgLineFab -> StringUtils.isNotEmpty(data.getFab_id_fk()) && data.getFab_id_fk().equals(toolToolgLineFab.getFab_id_fk())).collect(Collectors.toList()); break;
                //case "L": toolCorrelation_list = toolToolgLineFabCorrelation_list.stream().filter(toolToolgLineFab -> StringUtils.isNotEmpty(data.getLine_id_fk()) && data.getLine_id_fk().equals(toolToolgLineFab.getLine_id_fk())).collect(Collectors.toList()); break;
                //case "G": toolCorrelation_list = toolToolgLineFabCorrelation_list.stream().filter(toolToolgLineFab -> StringUtils.isNotEmpty(data.getToolg_id_fk()) && data.getToolg_id_fk().equals(toolToolgLineFab.getToolg_id_fk())).collect(Collectors.toList()); break;
                case "T": toolCorrelation_list = toolToolgLineFabCorrelation_list.stream().filter(toolToolgLineFab -> StringUtils.isNotEmpty(data.getTool_id_fk()) && data.getTool_id_fk().equals(toolToolgLineFab.getTool_id_fk())).collect(Collectors.toList()); break;
            }
            if (CollectionUtils.isNotEmpty(toolCorrelation_list)) {
                data.setFab_id_fk(toolCorrelation_list.get(0).getFab_id_fk());
                data.setFab_id(toolCorrelation_list.get(0).getFab_id());
                data.setLine_id_fk(toolCorrelation_list.get(0).getLine_id_fk());
                data.setLine_id(toolCorrelation_list.get(0).getLine_id());
                data.setToolg_id_fk(toolCorrelation_list.get(0).getToolg_id_fk());
                data.setToolg_id(toolCorrelation_list.get(0).getToolg_id());
                data.setTool_id_fk(toolCorrelation_list.get(0).getTool_id());
                data.setTool_id(toolCorrelation_list.get(0).getTool_id());
                data.rate2Percent();
            }
        });
        if (StringUtils.isNotEmpty(conditions.getBay_id())) resultDataList = resultDataList.stream().filter(data -> Arrays.asList(conditions.getBay_id().split(",")).contains(data.getLine_id_fk())).collect(Collectors.toList());


        // G - 级别数据
        List<ActivationReportGridOut> gridOutBean_by_toolg_List = new ArrayList<>();
        resultDataList.stream().filter(data -> data.getToolg_id() != null).collect(Collectors.groupingBy(ActivationReportGridOut::getDay)).forEach((day, by_day_list) -> {
            by_day_list.stream().collect(Collectors.groupingBy(ActivationReportGridOut::getToolg_id)).forEach((toolg, by_day_toolg_list) -> {
                ActivationReportGridOut gridOutBean = new ActivationReportGridOut();
                gridOutBean.setRun_rate(getAverageRate(by_day_toolg_list.stream().mapToDouble(data -> data.getRun_rate()).sum(), by_day_toolg_list.size(), 2));
                gridOutBean.setDown_rate(getAverageRate(by_day_toolg_list.stream().mapToDouble(data -> data.getDown_rate()).sum(), by_day_toolg_list.size(), 2));
                gridOutBean.setIdle_rate(getAverageRate(by_day_toolg_list.stream().mapToDouble(data -> data.getIdle_rate()).sum(), by_day_toolg_list.size(), 2));
                gridOutBean.setPm_rate(getAverageRate(by_day_toolg_list.stream().mapToDouble(data -> data.getPm_rate()).sum(), by_day_toolg_list.size(), 2));
                gridOutBean.setStop_rate(getAverageRate(by_day_toolg_list.stream().mapToDouble(data -> data.getStop_rate()).sum(), by_day_toolg_list.size(), 2));
                gridOutBean.setOther_rate(getAverageRate(by_day_toolg_list.stream().mapToDouble(data -> data.getOther_rate()).sum(), by_day_toolg_list.size(), 2));
                gridOutBean.setActivation_rate(gridOutBean.getRun_rate());
                gridOutBean.setLevel("G");
                gridOutBean.setDay(day);
                gridOutBean.setFab_id_fk(by_day_toolg_list.get(0).getFab_id_fk());
                gridOutBean.setFab_id(by_day_toolg_list.get(0).getFab_id());
                gridOutBean.setToolg_id_fk(by_day_toolg_list.get(0).getToolg_id_fk());
                gridOutBean.setToolg_id(toolg);
                gridOutBean.rate2Percent();
                gridOutBean_by_toolg_List.add(gridOutBean);
            });
        });

        // L - 级别数据
        List<ActivationReportGridOut> gridOutBean_by_line_List = new ArrayList<>();
        resultDataList.stream().filter(data -> data.getLine_id() != null).collect(Collectors.groupingBy(ActivationReportGridOut::getDay)).forEach((day, by_day_list) -> {
            by_day_list.stream().filter(data -> {
                if ("101A".equals(data.getFab_id_fk())) {
                    return "$TSWYS".equals(data.getToolg_id_fk());
                }else if("102A".equals(data.getFab_id_fk())) {
                    return "102A-TCH".equals(data.getToolg_id_fk());
                }else if("102B".equals(data.getFab_id_fk())) {
                    return "102B-TCH".equals(data.getToolg_id_fk());
                }else {
                    return false;
                }
            }).collect(Collectors.groupingBy(ActivationReportGridOut::getLine_id)).forEach((line, by_day_line_list) -> {
                ActivationReportGridOut gridOutBean = new ActivationReportGridOut();
                gridOutBean.setRun_rate(getAverageRate(by_day_line_list.stream().mapToDouble(data -> data.getRun_rate()).sum(), by_day_line_list.size(), 2));
                gridOutBean.setDown_rate(getAverageRate(by_day_line_list.stream().mapToDouble(data -> data.getDown_rate()).sum(), by_day_line_list.size(), 2));
                gridOutBean.setIdle_rate(getAverageRate(by_day_line_list.stream().mapToDouble(data -> data.getIdle_rate()).sum(), by_day_line_list.size(), 2));
                gridOutBean.setPm_rate(getAverageRate(by_day_line_list.stream().mapToDouble(data -> data.getPm_rate()).sum(), by_day_line_list.size(), 2));
                gridOutBean.setStop_rate(getAverageRate(by_day_line_list.stream().mapToDouble(data -> data.getStop_rate()).sum(), by_day_line_list.size(), 2));
                gridOutBean.setOther_rate(getAverageRate(by_day_line_list.stream().mapToDouble(data -> data.getOther_rate()).sum(), by_day_line_list.size(), 2));
                gridOutBean.setActivation_rate(gridOutBean.getRun_rate());
                gridOutBean.setLevel("L");
                gridOutBean.setDay(day);
                gridOutBean.setFab_id_fk(by_day_line_list.get(0).getFab_id_fk());
                gridOutBean.setFab_id(by_day_line_list.get(0).getFab_id());
                gridOutBean.setLine_id_fk(by_day_line_list.get(0).getLine_id_fk());
                gridOutBean.setLine_id(line);
                gridOutBean.rate2Percent();
                gridOutBean_by_line_List.add(gridOutBean);
            });
        });

        List<ActivationReportGridOut> final_gridOutBean_List = new ArrayList<>();
        final_gridOutBean_List.addAll(resultDataList);
        final_gridOutBean_List.addAll(gridOutBean_by_toolg_List);
        final_gridOutBean_List.addAll(gridOutBean_by_line_List);
        return final_gridOutBean_List;
    }




    /*** 查询设备、设备组、线别、厂别之间的关联关系，及中文描述 **/
    private List<ToolToolgLineFabCorrelation> getTool_toolg_line_fab_correlation() {
        final SqlVo sqlVo = new SqlVo();
        sqlVo.appendSql("SELECT");
        sqlVo.appendSql("A.TOOL_ID AS tool_id_fk,");
        sqlVo.appendSql("A.TOOL_DSC as tool_id,");
        sqlVo.appendSql("B.TOOLG_ID as toolg_id_fk,");
        sqlVo.appendSql("b_3.data_desc as toolg_id,");
        sqlVo.appendSql("A.BAY_ID as line_id_fk,");
        sqlVo.appendSql("b_2.data_desc as line_id,");
        sqlVo.appendSql("A.FAB_ID_FK as fab_id_fk,");
        sqlVo.appendSql("b_1.data_desc as fab_id");
        sqlVo.appendSql("FROM");
        sqlVo.appendSql("bis_tool A");
        sqlVo.appendSql("LEFT JOIN bis_tool_gr B ON A.ROOT_TOOL_ID = B.TOOL_ID_FK");
        sqlVo.appendSql("LEFT JOIN (select data_ext,data_desc from icim_jn.bis_data where data_cate = 'FBID') b_1 ON A.FAB_ID_FK = b_1.data_ext");
        sqlVo.appendSql("LEFT JOIN (select data_ext,data_desc from icim_jn.bis_data where data_cate = 'AREA') b_2 ON A.BAY_ID = b_2.data_ext");
        sqlVo.appendSql("LEFT JOIN (select data_ext,data_desc from icim_jn.bis_data where data_cate = 'TOLG') b_3 ON B.TOOLG_ID = b_3.data_ext");
        sqlVo.appendSql("ORDER BY A.TOOL_DSC");

        //List<ToolToolgLineFabCorrelation> tool_toolg_line_fab_list = dao.findBySQL(sqlVo.getSql());
        //List<ToolToolgLineFabCorrelation> tool_toolg_line_fab_list = LKingDao.queryListBySql(sqlVo, ToolToolgLineFabCorrelation.class);
        List<ToolToolgLineFabCorrelation> tool_toolg_line_fab_list = new ArrayList<>();
        List<Object[]> objList = dao.findBySQL(sqlVo.getSql());
        for (Object[] objects : objList) {
            ToolToolgLineFabCorrelation bean = new ToolToolgLineFabCorrelation();
            bean.setTool_id_fk(objects[0].toString());
            bean.setTool_id(objects[1].toString());
            bean.setToolg_id_fk(objects[2].toString());
            bean.setToolg_id(objects[3].toString());
            bean.setLine_id_fk(objects[4].toString());
            bean.setLine_id(objects[5].toString());
            bean.setFab_id_fk(objects[6].toString());
            bean.setFab_id(objects[7].toString());
            tool_toolg_line_fab_list.add(bean);
        }
        return tool_toolg_line_fab_list;
    }

    /*** 给定时间段拆分成每一天 **/
    private static List<String> getDateStrList(String beginDateStr, String endDateStr) {
        List<String> date_list = new ArrayList<>();
        try {
            GregorianCalendar begin_GC = new GregorianCalendar();
            begin_GC.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(beginDateStr));

            GregorianCalendar end_GC = new GregorianCalendar();
            end_GC.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(endDateStr));

            while(begin_GC.getTime().compareTo(end_GC.getTime()) <= 0) {
                int year = begin_GC.get(Calendar.YEAR);
                int month = begin_GC.get(Calendar.MONTH)+1;
                int day = begin_GC.get(Calendar.DAY_OF_MONTH);
                String yearMonthDay_str = String.valueOf(year)  + "-" +  (month<10?0+String.valueOf(month):String.valueOf(month))  + "-" +  (day<10?0+String.valueOf(day):String.valueOf(day));
                date_list.add(yearMonthDay_str);
                //date_list.add(begin_GC.get(Calendar.YEAR) + "-" + (begin_GC.get(Calendar.MONTH)+1 + "-" +  begin_GC.get(Calendar.DAY_OF_MONTH)));
                begin_GC.add(Calendar.DAY_OF_MONTH,1);
            }
            return date_list;
        }
        catch(Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
